package nix.dbutils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import nix.jdbc.JdbcUtils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

public class TestQueryRunner {

	@Test
	public void update() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); 
		qr.update("insert into stu (id, name) values (?, ?)", 3, "wangWu");
	}
	
	@Test
	public void single() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); 
		ResultSetHandler<Student> rsh = new ResultSetHandler<Student>() {
			@Override
			public Student handle(ResultSet rs) throws SQLException {
				if (!rs.next()) return null;
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				return stu;
			}			
		};
		Student stu = qr.query("select * from stu where id = ?", rsh, 3);
		System.out.println(stu);
	}
	
	@Test
	public void single2() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); 
		// 使用DbUtils提供的ResultSetHandler实现类 BeanHandler（内部使用反射）
		Student stu = qr.query("select * from stu where id = ?", new BeanHandler<Student>(Student.class), 3);
		System.out.println(stu);
	}
	
	@Test
	public void multi() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); 
		// BeanListHandler
		List<Student> list = qr.query("select * from stu", new BeanListHandler<Student>(Student.class));
		for (Student student : list) {
			System.out.println(student);
		}	
	}
	
	@Test
	public void singlMap() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		// MapHandler
		Map<String, Object> map = qr.query("select * from stu where id = ?", new MapHandler(), 3);
		for (Entry<String, Object> entry : map.entrySet()) {
			System.out.print(entry.getKey() + "=" + entry.getValue() + ", ");
		}	
	}
	
	@Test
	public void multiMap() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		// MapListHandler
		List<Map<String, Object>> list = qr.query("select * from stu", new MapListHandler());
		System.out.println(list);
	}
	
	@Test
	public void scalar() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		// ScalarHandler，驱动版本不同返回值类型不同（Long、Integer、BigInt），因此最好使用Number接收
		Object count = qr.query("select count(*) from stu", new ScalarHandler());
		System.out.println(count.getClass().getName() + ": " + count);
		Number cnt = (Number)count;
		System.out.println(cnt.longValue());
	}
}
